package com.tbynet.jwp.repository.impl;

import java.util.*;
import java.util.stream.Collectors;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.tbynet.jwp.framework.aop.Aop;
import com.tbynet.jwp.framework.core._JwpServiceProvider;
import com.tbynet.jwp.model.TermTaxonomy;
import com.tbynet.jwp.model.Terms;
import com.tbynet.jwp.repository.spi.TermService;

/**
 * 分类项服务实现类
 * @ClassName: TermServiceProvider
 * @Description: 负责分类项数据的获取、添加、修改、删除功能的具体实现，职责单一
 * @Author: 佰亿互联
 * @Date: 2025年11月4日
 *
 * @Copyright: 2025 www.tbynet.com Inc. All rights reserved.
 * 注意: 本内容仅限于内部传阅，禁止外泄以及用于其他的商业目
 */
@Aop(TermService.class)
public class TermServiceImpl extends _JwpServiceProvider<Terms> implements TermService {

    @Override
    protected String buildSearchWhereSql(String keyword, List<Object> params) {
        if (StrKit.notBlank(keyword)) {
            params.add("%" + keyword + "%");
            params.add("%" + keyword + "%");
            return " WHERE (t.name LIKE ? OR t.slug LIKE ?)";
        }
        return " WHERE 1=1";
    }

    @Override
    protected String getDefaultOrderBy() {
        return "t.term_id DESC";
    }

    @Override
    protected String getBasicFields() {
        return "t.term_id, t.name, t.slug, t.term_group";
    }

    // ============ 查询相关方法实现 ============

    @Override
    public Terms getTerm(Object termId, String taxonomy) {
        return executeWithResultLogging("getTerm", () -> {
            validateNotNull(termId, "分类项ID不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT t.*, tt.* FROM wp_terms AS t " +
                    "INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id " +
                    "WHERE t.term_id=? AND tt.taxonomy=? LIMIT 1";
            return getDao().findFirst(sql, termId, taxonomy);
        }, termId, taxonomy);
    }

    @Override
    public Terms getTerm(Object objectId, Object termTaxonomyId, String taxonomy) {
        return executeWithResultLogging("getTerm", () -> {
            validateNotNull(objectId, "对象ID不能为空");
            validateNotNull(termTaxonomyId, "分类法ID不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT t.* FROM wp_terms AS t " +
                    "INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id " +
                    "INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id " +
                    "WHERE tr.object_id=? AND tr.term_taxonomy_id=? AND tt.taxonomy=? LIMIT 1";
            return getDao().findFirst(sql, objectId, termTaxonomyId, taxonomy);
        }, objectId, termTaxonomyId, taxonomy);
    }

    @Override
    public List<Terms> getTerms(String taxonomy) {
        return executeWithResultLogging("getTerms", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT t.*, tt.* FROM wp_terms AS t " +
                    "INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy=? ORDER BY t.name ASC";
            return getDao().find(sql, taxonomy);
        }, taxonomy);
    }

    @Override
    public List<Terms> getTerms(String taxonomy, String q) {
        return executeWithResultLogging("getTerms", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT t.*, tt.* FROM wp_terms AS t " +
                    "INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy=? AND t.name like ? ORDER BY t.name ASC";
            return getDao().find(sql, taxonomy, "%" + q + "%");
        }, taxonomy, q);
    }

    @Override
    public Map<String, List<Terms>> getParents(String taxonomy) {
        return executeWithResultLogging("getParents", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");

            Map<String, List<Terms>> map = new HashMap<>();
            List<Terms> terms = getTerms(taxonomy);

            for (Terms term : terms) {
                String parentKey = String.valueOf(term.getBigInteger("parent"));
                map.computeIfAbsent(parentKey, k -> new ArrayList<>()).add(term);
            }

            return map;
        }, taxonomy);
    }

    @Override
    public List<Terms> getTerms(Object objectId, String taxonomy) {
        return executeWithResultLogging("getTerms", () -> {
            validateNotNull(objectId, "对象ID不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT t.* FROM wp_terms AS t " +
                    "INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id " +
                    "INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id " +
                    "WHERE tr.object_id=? AND tt.taxonomy=?";
            return getDao().find(sql, objectId, taxonomy);
        }, objectId, taxonomy);
    }

    @Override
    public Page<Terms> search(int pageNumber, int pageSize, String taxonomy, String q) {
        return executeWithResultLogging("search", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");

            List<Object> params = new ArrayList<>();
            StringBuilder sql = new StringBuilder("FROM wp_terms AS t " +
                    "LEFT JOIN wp_term_taxonomy AS tt ON t.term_id=tt.term_id " +
                    "WHERE 1=1");
            sql.append(" AND tt.taxonomy=? ");
            params.add(taxonomy);

            if (StrKit.notBlank(q)) {
                sql.append(" AND t.name LIKE ? ");
                params.add("%" + q + "%");
            }
            sql.append(" ORDER BY t.name ASC ");

            return getDao().paginate(pageNumber, pageSize, "SELECT t.*, tt.*", sql.toString(), params.toArray());
        }, pageNumber, pageSize, taxonomy, q);
    }

    // ============ 分类项基础操作 ============

    @Override
    public Terms getTermWithTaxonomy(Object termId) {
        return executeWithResultLogging("getTermWithTaxonomy", () -> {
            validateNotNull(termId, "分类项ID不能为空");

            String sql = "SELECT t.*, tt.* FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE t.term_id = ? LIMIT 1";
            return getDao().findFirst(sql, termId);
        }, termId);
    }

    @Override
    public Terms getTermBySlug(String slug) {
        return executeWithResultLogging("getTermBySlug", () -> {
            validateNotBlank(slug, "分类项别名不能为空");

            String sql = "SELECT * FROM wp_terms WHERE slug = ? LIMIT 1";
            return getDao().findFirst(sql, slug);
        }, slug);
    }

    @Override
    public Terms getTermByNameAndTaxonomy(String name, String taxonomy) {
        return executeWithResultLogging("getTermByNameAndTaxonomy", () -> {
            validateNotBlank(name, "分类项名称不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT t.* FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE t.name=? AND tt.taxonomy=? LIMIT 1";
            return getDao().findFirst(sql, name, taxonomy);
        }, name, taxonomy);
    }

    @Override
    public Terms getTermBySlugAndTaxonomy(String slug, String taxonomy) {
        return executeWithResultLogging("getTermBySlugAndTaxonomy", () -> {
            validateNotBlank(slug, "分类项别名不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT t.* FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE t.slug=? AND tt.taxonomy=? LIMIT 1";
            return getDao().findFirst(sql, slug, taxonomy);
        }, slug, taxonomy);
    }

    // ============ 分类法相关操作 ============

    @Override
    public List<String> getTaxonomies() {
        return executeWithResultLogging("getTaxonomies", () -> {
            String sql = "SELECT DISTINCT taxonomy FROM wp_term_taxonomy ORDER BY taxonomy";
            List<Record> records = Db.find(sql);
            return records.stream()
                    .map(record -> record.getStr("taxonomy"))
                    .collect(Collectors.toList());
        });
    }

    @Override
    public List<Terms> getTermsByTaxonomy(String taxonomy) {
        return getTerms(taxonomy);
    }

    @Override
    public List<Terms> getTermsByTaxonomy(String taxonomy, boolean hierarchical) {
        return executeWithResultLogging("getTermsByTaxonomy", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT t.*, tt.* FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy=? " +
                    (hierarchical ? "ORDER BY tt.parent ASC, t.name ASC" : "ORDER BY t.name ASC");
            return getDao().find(sql, taxonomy);
        }, taxonomy, hierarchical);
    }

    @Override
    public List<Terms> getChildTerms(String taxonomy, Object parentId) {
        return executeWithResultLogging("getChildTerms", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");
            validateNotNull(parentId, "父级ID不能为空");

            String sql = "SELECT t.*, tt.* FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy=? AND tt.parent=? " +
                    "ORDER BY t.name ASC";
            return getDao().find(sql, taxonomy, parentId);
        }, taxonomy, parentId);
    }

    @Override
    public List<Terms> getTopLevelTerms(String taxonomy) {
        return executeWithResultLogging("getTopLevelTerms", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT t.*, tt.* FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy=? AND tt.parent=0 " +
                    "ORDER BY t.name ASC";
            return getDao().find(sql, taxonomy);
        }, taxonomy);
    }

    // ============ 对象关联查询 ============

    @Override
    public List<Terms> getTermsByObject(Object objectId, String taxonomy) {
        return executeWithResultLogging("getTermsByObject", () -> {
            validateNotNull(objectId, "对象ID不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT t.* FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "INNER JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id " +
                    "WHERE tr.object_id=? AND tt.taxonomy=? " +
                    "ORDER BY t.name ASC";
            return getDao().find(sql, objectId, taxonomy);
        }, objectId, taxonomy);
    }

    @Override
    public List<Terms> getTermsByObject(Object objectId) {
        return executeWithResultLogging("getTermsByObject", () -> {
            validateNotNull(objectId, "对象ID不能为空");

            String sql = "SELECT t.*, tt.taxonomy FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "INNER JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id " +
                    "WHERE tr.object_id=? " +
                    "ORDER BY tt.taxonomy, t.name ASC";
            return getDao().find(sql, objectId);
        }, objectId);
    }

    @Override
    public boolean hasTerm(Object objectId, Object termId) {
        return executeWithBooleanLogging("hasTerm", () -> {
            validateNotNull(objectId, "对象ID不能为空");
            validateNotNull(termId, "分类项ID不能为空");

            String sql = "SELECT COUNT(*) FROM wp_term_relationships tr " +
                    "INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id " +
                    "WHERE tr.object_id=? AND tt.term_id=?";
            Long count = Db.queryLong(sql, objectId, termId);
            return count != null && count > 0;
        }, objectId, termId);
    }

    @Override
    public boolean setObjectTerms(Object objectId, Object[] termIds, String taxonomy) {
        return executeWithBooleanLogging("setObjectTerms", () -> {
            validateNotNull(objectId, "对象ID不能为空");
            validateNotNull(termIds, "分类项ID数组不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");

            // 先删除现有的关联
            String deleteSql = "DELETE tr FROM wp_term_relationships tr " +
                    "INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id " +
                    "WHERE tr.object_id=? AND tt.taxonomy=?";
            Db.update(deleteSql, objectId, taxonomy);

            // 添加新的关联
            boolean success = true;
            for (Object termId : termIds) {
                if (!addObjectTerm(objectId, termId, taxonomy)) {
                    success = false;
                    break;
                }
            }
            return success;
        }, objectId, Arrays.toString(termIds), taxonomy);
    }

    @Override
    public boolean addObjectTerm(Object objectId, Object termId, String taxonomy) {
        return executeWithBooleanLogging("addObjectTerm", () -> {
            validateNotNull(objectId, "对象ID不能为空");
            validateNotNull(termId, "分类项ID不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");

            // 获取term_taxonomy_id
            String sql = "SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE term_id=? AND taxonomy=?";
            Record record = Db.findFirst(sql, termId, taxonomy);
            if (record == null) {
                log.warn("未找到对应的分类法记录，termId: {}, taxonomy: {}", termId, taxonomy);
                return false;
            }

            Object termTaxonomyId = record.get("term_taxonomy_id");

            // 检查是否已存在关联
            String checkSql = "SELECT COUNT(*) FROM wp_term_relationships WHERE object_id=? AND term_taxonomy_id=?";
            Long exists = Db.queryLong(checkSql, objectId, termTaxonomyId);
            if (exists != null && exists > 0) {
                return true; // 已存在，返回成功
            }

            // 插入新关联
            String insertSql = "INSERT INTO wp_term_relationships (object_id, term_taxonomy_id) VALUES (?, ?)";
            int result = Db.update(insertSql, objectId, termTaxonomyId);
            return result > 0;
        }, objectId, termId, taxonomy);
    }

    @Override
    public boolean removeObjectTerm(Object objectId, Object termId) {
        return executeWithBooleanLogging("removeObjectTerm", () -> {
            validateNotNull(objectId, "对象ID不能为空");
            validateNotNull(termId, "分类项ID不能为空");

            String sql = "DELETE tr FROM wp_term_relationships tr " +
                    "INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id " +
                    "WHERE tr.object_id=? AND tt.term_id=?";
            int result = Db.update(sql, objectId, termId);
            return result > 0;
        }, objectId, termId);
    }

    // ============ 标签相关操作 ============

    @Override
    public List<Terms> getTags() {
        return executeWithResultLogging("getTags", () -> {
            String sql = "SELECT t.* FROM wp_terms t " +
                    "JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy = 'post_tag' " +
                    "ORDER BY t.name ASC";
            return getDao().find(sql);
        });
    }

    @Override
    public List<Terms> getTagsByPostId(Object postId) {
        return executeWithResultLogging("getTagsByPostId", () -> {
            validateNotNull(postId, "文章ID不能为空");

            String sql = "SELECT t.* FROM wp_terms t " +
                    "JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id " +
                    "WHERE tr.object_id = ? AND tt.taxonomy = 'post_tag' " +
                    "ORDER BY t.name ASC";
            return getDao().find(sql, postId);
        }, postId);
    }

    @Override
    public Page<Terms> searchTags(int pageNumber, int pageSize, String keyword) {
        return executeWithResultLogging("searchTags", () -> {
            List<Object> params = new ArrayList<>();
            StringBuilder sqlBuilder = new StringBuilder("FROM wp_terms t ");
            sqlBuilder.append("JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id ");
            sqlBuilder.append("WHERE tt.taxonomy = 'post_tag' ");

            if (keyword != null && !keyword.trim().isEmpty()) {
                sqlBuilder.append("AND (t.name LIKE ? OR t.slug LIKE ?) ");
                String likeKeyword = "%" + keyword + "%";
                params.add(likeKeyword);
                params.add(likeKeyword);
            }

            sqlBuilder.append("ORDER BY t.name ASC");

            String selectSql = "SELECT t.* " + sqlBuilder.toString();
            String countSql = "SELECT COUNT(*) " + sqlBuilder.toString();

            List<Terms> tagList = getDao().find(selectSql, params.toArray());
            Long totalRow = Db.queryLong(countSql, params.toArray());

            int totalPage = (int) Math.ceil((double) totalRow / pageSize);
            return new Page<>(tagList, pageNumber, pageSize, totalPage, totalRow.intValue());
        }, pageNumber, pageSize, keyword);
    }

    @Override
    public List<Terms> getPopularTags(int limit) {
        return executeWithResultLogging("getPopularTags", () -> {
            validateNotNull(limit, "限制数量不能为空");
            int actualLimit = Math.min(limit, 100); // 限制最大数量

            String sql = "SELECT t.*, tt.count FROM wp_terms t " +
                    "JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy = 'post_tag' " +
                    "ORDER BY tt.count DESC, t.name ASC " +
                    "LIMIT ?";
            return getDao().find(sql, actualLimit);
        }, limit);
    }

    // ============ 分类目录相关操作 ============

    @Override
    public List<Terms> getCategories() {
        return executeWithResultLogging("getCategories", () -> {
            String sql = "SELECT t.*, tt.* FROM wp_terms t " +
                    "JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy = 'category' " +
                    "ORDER BY t.name ASC";
            return getDao().find(sql);
        });
    }

    @Override
    public List<Terms> getCategoriesByPostId(Object postId) {
        return executeWithResultLogging("getCategoriesByPostId", () -> {
            validateNotNull(postId, "文章ID不能为空");

            String sql = "SELECT t.* FROM wp_terms t " +
                    "JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id " +
                    "WHERE tr.object_id = ? AND tt.taxonomy = 'category' " +
                    "ORDER BY t.name ASC";
            return getDao().find(sql, postId);
        }, postId);
    }

    @Override
    public Terms getDefaultCategory() {
        return executeWithResultLogging("getDefaultCategory", () -> {
            String sql = "SELECT t.* FROM wp_terms t " +
                    "JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy = 'category' " +
                    "ORDER BY t.term_id ASC LIMIT 1";
            return getDao().findFirst(sql);
        });
    }

    // ============ 层级关系操作 ============

    @Override
    public Terms getParentTerm(Object termId) {
        return executeWithResultLogging("getParentTerm", () -> {
            validateNotNull(termId, "分类项ID不能为空");

            String sql = "SELECT t.* FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "INNER JOIN wp_term_taxonomy parent_tt ON tt.parent = parent_tt.term_id " +
                    "INNER JOIN wp_terms parent_t ON parent_tt.term_id = parent_t.term_id " +
                    "WHERE t.term_id = ? LIMIT 1";
            return getDao().findFirst(sql, termId);
        }, termId);
    }

    @Override
    public List<Terms> getAncestorTerms(Object termId) {
        return executeWithResultLogging("getAncestorTerms", () -> {
            validateNotNull(termId, "分类项ID不能为空");

            // 使用递归CTE获取所有祖先（MySQL 8.0+）
            String sql = "WITH RECURSIVE ancestor_terms AS (" +
                    "  SELECT t.*, tt.parent, 0 as level " +
                    "  FROM wp_terms t " +
                    "  INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "  WHERE t.term_id = ? " +
                    "  UNION ALL " +
                    "  SELECT t.*, tt.parent, at.level + 1 " +
                    "  FROM wp_terms t " +
                    "  INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "  INNER JOIN ancestor_terms at ON tt.term_id = at.parent " +
                    "  WHERE at.parent != 0" +
                    ") " +
                    "SELECT * FROM ancestor_terms WHERE level > 0 ORDER BY level DESC";

            return getDao().find(sql, termId);
        }, termId);
    }

    @Override
    public List<Terms> getDescendantTerms(Object termId) {
        return executeWithResultLogging("getDescendantTerms", () -> {
            validateNotNull(termId, "分类项ID不能为空");

            // 使用递归CTE获取所有子孙（MySQL 8.0+）
            String sql = "WITH RECURSIVE descendant_terms AS (" +
                    "  SELECT t.*, tt.parent, 0 as level " +
                    "  FROM wp_terms t " +
                    "  INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "  WHERE t.term_id = ? " +
                    "  UNION ALL " +
                    "  SELECT t.*, tt.parent, dt.level + 1 " +
                    "  FROM wp_terms t " +
                    "  INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "  INNER JOIN descendant_terms dt ON tt.parent = dt.term_id " +
                    ") " +
                    "SELECT * FROM descendant_terms WHERE level > 0 ORDER BY level ASC, name ASC";

            return getDao().find(sql, termId);
        }, termId);
    }

    @Override
    public Map<Long, List<Terms>> getTermTree(String taxonomy) {
        return executeWithResultLogging("getTermTree", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");

            List<Terms> allTerms = getTermsByTaxonomy(taxonomy, true);
            Map<Long, List<Terms>> treeMap = new HashMap<>();

            for (Terms term : allTerms) {
                Long parentId = term.getLong("parent");
                treeMap.computeIfAbsent(parentId, k -> new ArrayList<>()).add(term);
            }

            return treeMap;
        }, taxonomy);
    }

    // ============ 搜索和过滤 ============

    @Override
    public List<Terms> searchTerms(String taxonomy, String keyword) {
        return executeWithResultLogging("searchTerms", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT t.*, tt.* FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy=? AND (t.name LIKE ? OR t.slug LIKE ?) " +
                    "ORDER BY t.name ASC";
            return getDao().find(sql, taxonomy, "%" + keyword + "%", "%" + keyword + "%");
        }, taxonomy, keyword);
    }

    @Override
    public Page<Terms> search(int pageNumber, int pageSize, String taxonomy, String keyword, Object parentId) {
        return executeWithResultLogging("search", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");

            List<Object> params = new ArrayList<>();
            StringBuilder sql = new StringBuilder("FROM wp_terms AS t " +
                    "LEFT JOIN wp_term_taxonomy AS tt ON t.term_id=tt.term_id " +
                    "WHERE 1=1");
            sql.append(" AND tt.taxonomy=? ");
            params.add(taxonomy);

            if (StrKit.notBlank(keyword)) {
                sql.append(" AND (t.name LIKE ? OR t.slug LIKE ?) ");
                params.add("%" + keyword + "%");
                params.add("%" + keyword + "%");
            }

            if (parentId != null) {
                sql.append(" AND tt.parent=? ");
                params.add(parentId);
            }

            sql.append(" ORDER BY t.name ASC ");

            return getDao().paginate(pageNumber, pageSize, "SELECT t.*, tt.*", sql.toString(), params.toArray());
        }, pageNumber, pageSize, taxonomy, keyword, parentId);
    }

    @Override
    public List<Terms> getEmptyTerms(String taxonomy) {
        return executeWithResultLogging("getEmptyTerms", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT t.*, tt.* FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy=? AND tt.count = 0 " +
                    "ORDER BY t.name ASC";
            return getDao().find(sql, taxonomy);
        }, taxonomy);
    }

    // ============ 检查和验证 ============

    @Override
    public boolean isTermNameUnique(String name, String taxonomy, Object excludeTermId) {
        return executeWithBooleanLogging("isTermNameUnique", () -> {
            validateNotBlank(name, "分类项名称不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");

            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM wp_terms t ")
                    .append("INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id ")
                    .append("WHERE t.name=? AND tt.taxonomy=?");

            List<Object> params = new ArrayList<>();
            params.add(name);
            params.add(taxonomy);

            if (excludeTermId != null) {
                sql.append(" AND t.term_id != ?");
                params.add(excludeTermId);
            }

            Long count = Db.queryLong(sql.toString(), params.toArray());
            return count != null && count == 0;
        }, name, taxonomy, excludeTermId);
    }

    @Override
    public boolean isTermSlugUnique(String slug, String taxonomy, Object excludeTermId) {
        return executeWithBooleanLogging("isTermSlugUnique", () -> {
            validateNotBlank(slug, "分类项别名不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");

            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM wp_terms t ")
                    .append("INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id ")
                    .append("WHERE t.slug=? AND tt.taxonomy=?");

            List<Object> params = new ArrayList<>();
            params.add(slug);
            params.add(taxonomy);

            if (excludeTermId != null) {
                sql.append(" AND t.term_id != ?");
                params.add(excludeTermId);
            }

            Long count = Db.queryLong(sql.toString(), params.toArray());
            return count != null && count == 0;
        }, slug, taxonomy, excludeTermId);
    }

    @Override
    public boolean hasChildTerms(Object termId) {
        return executeWithBooleanLogging("hasChildTerms", () -> {
            validateNotNull(termId, "分类项ID不能为空");

            String sql = "SELECT COUNT(*) FROM wp_term_taxonomy WHERE parent=?";
            Long count = Db.queryLong(sql, termId);
            return count != null && count > 0;
        }, termId);
    }

    @Override
    public boolean isTermInUse(Object termId) {
        return executeWithBooleanLogging("isTermInUse", () -> {
            validateNotNull(termId, "分类项ID不能为空");

            String sql = "SELECT COUNT(*) FROM wp_term_relationships tr " +
                    "INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id " +
                    "WHERE tt.term_id=?";
            Long count = Db.queryLong(sql, termId);
            return count != null && count > 0;
        }, termId);
    }

    @Override
    public boolean hasOtherTaxonomy(Object termId, String excludeTaxonomy) {
        return executeWithBooleanLogging("hasOtherTaxonomy", () -> {
            validateNotNull(termId, "分类项ID不能为空");
            validateNotBlank(excludeTaxonomy, "排除分类法不能为空");

            String sql = "SELECT COUNT(*) FROM wp_term_taxonomy WHERE term_id=? AND taxonomy != ?";
            Long count = Db.queryLong(sql, termId, excludeTaxonomy);
            return count != null && count > 0;
        }, termId, excludeTaxonomy);
    }

    // ============ 统计操作 ============

    @Override
    public long getTermCount(Object termId) {
        return executeWithResultLogging("getTermCount", () -> {
            validateNotNull(termId, "分类项ID不能为空");

            String sql = "SELECT count FROM wp_term_taxonomy WHERE term_id=?";
            Long count = Db.queryLong(sql, termId);
            return count != null ? count : 0;
        }, termId);
    }

    @Override
    public long getTermCountByTaxonomy(String taxonomy) {
        return executeWithResultLogging("getTermCountByTaxonomy", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");

            String sql = "SELECT COUNT(*) FROM wp_term_taxonomy WHERE taxonomy=?";
            Long count = Db.queryLong(sql, taxonomy);
            return count != null ? count : 0;
        }, taxonomy);
    }

    @Override
    public boolean updateTermCount(Object termId) {
        return executeWithBooleanLogging("updateTermCount", () -> {
            validateNotNull(termId, "分类项ID不能为空");

            // 更新分类项计数
            String updateSql = "UPDATE wp_term_taxonomy tt " +
                    "SET tt.count = (" +
                    "  SELECT COUNT(*) FROM wp_term_relationships tr " +
                    "  WHERE tr.term_taxonomy_id = tt.term_taxonomy_id" +
                    ") " +
                    "WHERE tt.term_id=?";
            int result = Db.update(updateSql, termId);
            return result > 0;
        }, termId);
    }

    // ============ 工具方法 ============

    @Override
    public Terms getOrCreateTerm(String name, String taxonomy) {
        return executeWithResultLogging("getOrCreateTerm", () -> {
            validateNotBlank(name, "分类项名称不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");

            // 先尝试获取已存在的分类项
            Terms term = getTermByNameAndTaxonomy(name, taxonomy);
            if (term != null) {
                return term;
            }

            // 创建新的分类项
            term = new Terms();
            term.set("name", name);
            term.set("slug", generateUniqueSlug(name, taxonomy));
            term.set("term_group", 0L);

            if (term.save()) {
                // 创建对应的分类法记录
                TermTaxonomy termTaxonomy = new TermTaxonomy();
                termTaxonomy.set("term_id", term.get("term_id"));
                termTaxonomy.set("taxonomy", taxonomy);
                termTaxonomy.set("description", "");
                termTaxonomy.set("parent", 0L);
                termTaxonomy.set("count", 0L);

                if (termTaxonomy.save()) {
                    log.info("创建新分类项成功，名称: {}, ID: {}", name, term.get("term_id"));
                    return term;
                } else {
                    // 回滚：删除已创建的term
                    term.delete();
                    log.error("创建分类法记录失败，名称: {}", name);
                    return null;
                }
            } else {
                log.error("创建新分类项失败，名称: {}", name);
                return null;
            }
        }, name, taxonomy);
    }

    @Override
    public Terms getOrCreateTerm(String name, String taxonomy, Object parentId) {
        return executeWithResultLogging("getOrCreateTerm", () -> {
            validateNotBlank(name, "分类项名称不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");
            validateNotNull(parentId, "父级ID不能为空");

            // 先尝试获取已存在的分类项
            Terms term = getTermByNameAndTaxonomy(name, taxonomy);
            if (term != null) {
                return term;
            }

            // 创建新的分类项
            term = new Terms();
            term.set("name", name);
            term.set("slug", generateUniqueSlug(name, taxonomy));
            term.set("term_group", 0L);

            if (term.save()) {
                // 创建对应的分类法记录（指定父级）
                TermTaxonomy termTaxonomy = new TermTaxonomy();
                termTaxonomy.set("term_id", term.get("term_id"));
                termTaxonomy.set("taxonomy", taxonomy);
                termTaxonomy.set("description", "");
                termTaxonomy.set("parent", parentId);
                termTaxonomy.set("count", 0L);

                if (termTaxonomy.save()) {
                    log.info("创建新分类项成功，名称: {}, 父级ID: {}, ID: {}", name, parentId, term.get("term_id"));
                    return term;
                } else {
                    // 回滚：删除已创建的term
                    term.delete();
                    log.error("创建分类法记录失败，名称: {}, 父级ID: {}", name, parentId);
                    return null;
                }
            } else {
                log.error("创建新分类项失败，名称: {}, 父级ID: {}", name, parentId);
                return null;
            }
        }, name, taxonomy, parentId);
    }

    @Override
    public String generateUniqueSlug(String name, String taxonomy) {
        return executeWithResultLogging("generateUniqueSlug", () -> {
            validateNotBlank(name, "分类项名称不能为空");
            validateNotBlank(taxonomy, "分类法不能为空");

            String baseSlug = generateSlug(name);
            String slug = baseSlug;
            int counter = 1;

            // 检查slug是否唯一，如果不唯一则添加数字后缀
            while (!isTermSlugUnique(slug, taxonomy, null)) {
                slug = baseSlug + "-" + counter;
                counter++;

                // 防止无限循环
                if (counter > 100) {
                    slug = baseSlug + "-" + System.currentTimeMillis();
                    break;
                }
            }

            return slug;
        }, name, taxonomy);
    }

    @Override
    public int cleanupUnusedTerms(String taxonomy) {
        return executeWithResultLogging("cleanupUnusedTerms", () -> {
            validateNotBlank(taxonomy, "分类法不能为空");

            // 查找没有关联对象的分类项
            String sql = "SELECT tt.term_id FROM wp_term_taxonomy tt " +
                    "LEFT JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id " +
                    "WHERE tt.taxonomy=? AND tr.object_id IS NULL AND tt.count = 0";

            List<Record> unusedTerms = Db.find(sql, taxonomy);
            int deletedCount = 0;

            for (Record record : unusedTerms) {
                Object termId = record.get("term_id");
                if (deleteById(termId)) {
                    deletedCount++;
                }
            }

            log.info("清理未使用的分类项完成，分类法: {}, 清理数量: {}", taxonomy, deletedCount);
            return deletedCount;
        }, taxonomy);
    }

    @Override
    public int batchUpdateTermCounts(List<Object> termIds) {
        return executeWithResultLogging("batchUpdateTermCounts", () -> {
            validateNotNull(termIds, "分类项ID列表不能为空");
            if (termIds.isEmpty()) {
                return 0;
            }

            int updatedCount = 0;
            for (Object termId : termIds) {
                if (updateTermCount(termId)) {
                    updatedCount++;
                }
            }

            return updatedCount;
        }, termIds.size());
    }

    @Override
    public int batchDeleteTerms(List<Object> termIds, boolean force) {
        return executeWithResultLogging("batchDeleteTerms", () -> {
            validateNotNull(termIds, "分类项ID列表不能为空");
            if (termIds.isEmpty()) {
                return 0;
            }

            int deletedCount = 0;
            for (Object termId : termIds) {
                if (force || !isTermInUse(termId)) {
                    if (deleteById(termId)) {
                        deletedCount++;
                    }
                }
            }

            return deletedCount;
        }, termIds.size(), force);
    }

    // ============ 重写父类方法 ============

    @Override
    public Page<Terms> search(int pageNumber, int pageSize, String keyword) {
        // 默认搜索所有分类法的分类项
        return executeWithResultLogging("search", () -> {
            SearchContext context = new SearchContext(pageNumber, pageSize);
            List<Object> params = new ArrayList<>();
            String whereSql = buildSearchWhereSql(keyword, params);
            String sql = buildPageSql(whereSql, getDefaultOrderBy());
            return getDao().paginate(context.pageNumber, context.pageSize, "SELECT t.*", sql, params.toArray());
        }, pageNumber, pageSize, keyword);
    }

    /**
     * 生成slug
     */
    private String generateSlug(String name) {
        if (name == null) return "";

        // 简单的slug生成逻辑
        String slug = name.toLowerCase()
                .replaceAll("[^a-z0-9\\u4e00-\\u9fa5]", "-")
                .replaceAll("-+", "-")
                .replaceAll("^-|-$", "");

        if (slug.isEmpty()) {
            slug = "term-" + System.currentTimeMillis();
        }

        return slug;
    }

    // ============ 内部辅助类 ============

    /**
     * 搜索上下文 - 封装分页参数处理
     */
    private static class SearchContext {
        final int pageNumber;
        final int pageSize;

        SearchContext(int pageNumber, int pageSize) {
            this.pageNumber = Math.max(1, pageNumber);
            this.pageSize = validatePageSize(pageSize);
        }

        private int validatePageSize(int pageSize) {
            if (pageSize < 1) return 20; // 默认分页大小
            return Math.min(pageSize, 1000); // 最大分页大小
        }
    }
}